package com.jiaying.patient.dao;

import com.jiaying.patient.domain.Department;
import com.jiaying.patient.domain.Doctor;
import com.jiaying.patient.domain.Patient;
import com.jiaying.patient.domain.Record;

import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

/**
 * 患者dao
 */
public class PatientDao {

    /**
     * 修改患者信息
     */
    public void editPatient(Patient patient){

        // 1.声明两大对象
        //创建连接对象 Connection
        Connection connection=null;
        //创建执行sql语句的对象：PreparedStatement
        PreparedStatement stmt=null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/his_jiaying?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai","root","123456");


            String edit_sql="update tab_patient \n" +
                    "\n" +
                    "set real_name=?,gender=?,card_number=?,age=?,home_address=?,department_id=?\n" +
                    "where patient_id=?";

            stmt=connection.prepareStatement(edit_sql);
            stmt.setString(1,patient.getRealName());
            stmt.setInt(2,patient.getGender());
            stmt.setString(3,patient.getCardNumber());
            stmt.setInt(4,getAgeFromCardnumber(patient.getCardNumber()));
            stmt.setString(5,patient.getHomeAddress());
            stmt.setInt(6,patient.getDepartment().getDepartmentId());
            stmt.setInt(7,patient.getPatientId());

            stmt.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();  //这行代码也重要
        }


    }


    /**
     * 删除患者功能：根据就诊卡号删除
     */
    public void deletePatientById(Integer patientId){
        // 1.声明两大对象
        //创建连接对象 Connection
        Connection connection=null;
        //创建执行sql语句的对象：PreparedStatement
        PreparedStatement stmt=null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/his_jiaying?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai","root","123456");

           // String delete_sql="delete from tab_patient where patient_id="+patientId;  //有sql注入的风险

            /**  例子：
             * delete from tab_patient where patient_id=3 or "1"="1"
             */
            String delete_sql="delete from tab_patient where patient_id=?";

            stmt=connection.prepareStatement(delete_sql);
            stmt.setInt(1,patientId);

            stmt.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();  //这行代码也重要
        }

    }

    /**
     * 入院功能，其实是往数据库的患者表中保存一条数据，同时往病历本表中也要保存一条数据
     */
    public boolean insertPatient(Patient patient){
        boolean result=false;//默认入院不成功
        // 1.声明两大对象
        //创建连接对象 Connection
        Connection connection=null;
        //创建执行sql语句的对象：PreparedStatement
        PreparedStatement stmt=null;

        try{
            Class.forName("com.mysql.jdbc.Driver");
            connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/his_jiaying?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai","root","123456");

            //设置事务手动提交
            connection.setAutoCommit(false);

            //插入患者表的sql
            String sql_patient="insert into tab_patient(real_name,gender,card_number,age,home_address,state,department_id,record_id) values(?,?,?,?,?,?,?,?)";
            //病历本表的sql
            String sql_record="insert into tab_record(record_readme) values(?)";  //id调成自动递增


            stmt= connection.prepareStatement(sql_record);//record_id
            stmt.setString(1,patient.getRecord().getRecordReadme());
            int rows1=stmt.executeUpdate();

            ResultSet rs=stmt.executeQuery("select max(record_id) id from tab_record");
            int record_id=0;
            if(rs.next()){
                record_id=rs.getInt("id");
            }
            stmt=connection.prepareStatement(sql_patient);
            stmt.setString(1,patient.getRealName());
            stmt.setInt(2,patient.getGender());
            stmt.setString(3,patient.getCardNumber());
            stmt.setInt(4,getAgeFromCardnumber(patient.getCardNumber()));
            stmt.setString(5,patient.getHomeAddress());
            stmt.setInt(6,1);
            stmt.setInt(7,patient.getDepartment().getDepartmentId());
            stmt.setInt(8,record_id);

           // int a=10/0;

            int rows2=stmt.executeUpdate();
            /*
            给该入院方法添加事务的特性：要成功都成功，要失败全布回滚
            1.设置手动提交事务
            2.我们定义手动提交事务的代码
            3.我们定义失败时事务回滚的代码
             */

            //我们定义手动提交事务的代码
            if(rows1>0&&rows2>0){
                connection.commit();//提交事务
                result=true;  //把返回值也设置一下
            }


        }catch(Exception e){
            try {
                connection.rollback();  //回滚事务
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        return result;


    }


    /**
     *  根据就诊卡号查询患者
     */
    public Patient selectByPatientId(Integer patientId){
       Patient p=null;
        // 1.声明三大对象
        //创建连接对象 Connection
        Connection connection=null;
        //创建执行sql语句的对象：PreparedStatement
        PreparedStatement stmt=null;
        //创建接收查询结果的对象：Resultset
        ResultSet rs=null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/his_jiaying?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai","root","123456");
            String sql="select p.patient_id,p.real_name,p.gender,p.card_number,p.age,p.home_address,p.state,d.department_id,d.department_name,p.record_id from tab_patient p,tab_department d where p.department_id=d.department_id and patient_id=?";
            stmt=connection.prepareStatement(sql);
            stmt.setInt(1,patientId);
            rs=stmt.executeQuery();

            //因为这个结果只有一条数据，所有我们用一个对象来接收查询的结果

            if(rs.next()){  //指针下滑一行，指向首条数据
                //进入这里，代表查询结果中有数据
                p=new Patient();
                p.setAge(rs.getInt("age"));

                Department department=new Department();
                department.setDepartmentId(rs.getInt("department_id"));
                department.setDepartmentName(rs.getString("department_name"));

                p.setDepartment(department);
                p.setGender(rs.getInt("gender"));
                p.setHomeAddress(rs.getString("home_address"));
                p.setPatientId(rs.getInt("patient_id"));
                p.setRealName(rs.getString("real_name"));
                p.setState(rs.getInt("state"));

                Record record=new Record();
                record.setRecordId(rs.getInt("record_id"));
                p.setRecord(record);
                p.setCardNumber(rs.getString("card_number"));

            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return p;
    }

    /**
     * 定义工具方法：从身份证中获取年龄信息
     */
    public int getAgeFromCardnumber(String cardNumber){
        //当前年份--身份证年份
        //440104 19990908 5667
        if(cardNumber!=null){
            // 开区间闭区间  【  ）Integer.parseInt()
            int card_year=Integer.parseInt(cardNumber.substring(6,10));
            //获取当前年份
            Calendar calendar=Calendar.getInstance();
            int curr_year=calendar.get(Calendar.YEAR);

            return curr_year-card_year;
        }
        return 0;

    }

    /**
     * 查询所有患者
     */
    public List<Patient> selectAll(){
        List<Patient> patientList=new ArrayList<>();
     // 1.声明三大对象
        //创建连接对象 Connection
        Connection connection=null;
        //创建执行sql语句的对象：PreparedStatement
        PreparedStatement stmt=null;
        //创建接收查询结果的对象：Resultset
        ResultSet rs=null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/his_jiaying?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai","root","123456");
            String sql="select p.patient_id,p.real_name,p.gender,p.card_number,p.age,p.home_address,p.state," +
                    "d.department_id,d.department_name," +
                    "p.record_id from tab_patient p,tab_department d where p.department_id=d.department_id";
            stmt=connection.prepareStatement(sql);

            rs=stmt.executeQuery();

            //因为这个结果有很多条数据，所有我们用一个List数组来接收查询的结果

            while(rs.next()){  //指针下滑一行，指向首条数据
                //进入这里，代表查询结果中有数据
                Patient p=new Patient();
                p.setAge(rs.getInt("age"));

                Department department=new Department();
                department.setDepartmentId(rs.getInt("department_id"));
                department.setDepartmentName(rs.getString("department_name"));

                p.setDepartment(department);
                p.setGender(rs.getInt("gender"));
                p.setHomeAddress(rs.getString("home_address"));
                p.setPatientId(rs.getInt("patient_id"));
                p.setRealName(rs.getString("real_name"));
                p.setState(rs.getInt("state"));

                Record record=new Record();
                record.setRecordId(rs.getInt("record_id"));
                p.setRecord(record);
                p.setCardNumber(rs.getString("card_number"));

                patientList.add(p);  //小心空集合
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return patientList;
    }
}
